package com.report.doc;

import com.Constants;
import com.alibaba.fastjson.JSONObject;
import com.report.exp.ExpUtil;
import com.schedule.domain.ResultRealtimeCollector;
import com.schedule.domain.Template;
import com.util.ExcelUtil;
import com.util.Utils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.util.*;

import static com.util.Utils.*;

/**
 * word工具类
 */
public class ExcelService extends BaseDoc {

    /**
     * 单例
     */
    private static ExcelService excelService = null;

    public static ExcelService getInstance(Template template) {
        if (excelService == null) {
            excelService = new ExcelService(template);
        }
        return excelService;
    }

    private ExcelService(Template template) {
        this.docName = Constants.excel_2007;
        this.template = template;
    }

    /**
     * excel工具类
     */
    private ExcelUtil excelUtil = ExcelUtil.getInstance();

    /**
     * excel sheet数据
     */
    private Map<String, List<List<String>>> listMap;

    //    Map<String, List<ResultRealtimeCollector>> mapList = null;
//    Map<String, String> params = null;
    int rowStart = 0;
    int rowEnd = 0;
    int colStart = 0;
    int colEnd = 0;
    int maxRow = 0;
    List<Map<String, Object>> result = new LinkedList<>();
    Map<String, List<ResultRealtimeCollector>> mapList = null;

    /**
     * 初始化当前报表信息
     */
    @Override
    public void init() throws Exception {

        String templatePath = template.getPath();
        InputStream inStream = getInFileStream(templatePath);

        // 解析数据，key:sheet名称 value：sheet数据
        Map<String, List<List<String>>> listMap = excelUtil.readExcel(inStream);

        this.listMap = listMap;
//        System.out.println(listMap);

    }

    /**
     * 生成文件
     */
    @Override
    public String create() throws Exception {
//        // 语法工具
//        ExpUtil expUtil = new ExpUtil();
        // 模板信息
        Template template = this.template;
        // 获取模板，生成文件，
        String path = this.copyFile(template);

        int maxCol = 0;
        // 获取每一个sheet数据
        Set<String> keySet = listMap.keySet();
        List<String> keySetList = new ArrayList<>(keySet);
        for (int sheetIndex = 0; sheetIndex < keySetList.size(); sheetIndex++) {
            // sheet名称
            String sheetName = keySetList.get(sheetIndex);
            result = new LinkedList<>();

            // sheet数据
            List<List<String>> sheetList = listMap.get(sheetName);
            // 每行数据
            for (int rowIndex = 0; rowIndex < sheetList.size(); rowIndex++) {
                // 每列数据
                List<String> rows = sheetList.get(rowIndex);
                int size = rows.size();
                if (size > maxCol) {
                    maxCol = size;
                }
                analysis(rows);

            }
            writeFile(path, sheetIndex, result, maxCol);
        }
        return path;
    }

    /**
     * 解析
     *
     * @param rows 行数据
     * @return
     * @throws Exception
     */
    private void analysis(List<String> rows) throws Exception {
        // 语法工具
        ExpUtil expUtil = new ExpUtil();

        for (int colIndex = 0; colIndex < rows.size(); colIndex++) {
            // 每个单元格数据
            String cellText = rows.get(colIndex);
            if (isExp(cellText)) {
                // 解析表达式
                Map<String, Object> map = expUtil.getVal(cellText, this.template.getDefaultSQL());
                if(map == null){
                    continue;
                }
                // 表达式参数
                Map<String, String> params = (Map<String, String>) map.get("params");

                // 参数
                int rowIndex = Utils.convertInt(params.get(Constants.row_s));
                if (rowIndex != -1) {
                    rowStart = rowIndex;
                }

//                rowEnd = Utils.convertInt(params.get(Constants.row_e));
//                colStart = Utils.convertInt(params.get(Constants.col_s));
//                colEnd = Utils.convertInt(params.get(Constants.col_e));

                Object list1 = map.get("list");
                if (list1 != null) {
                    // 数据
                    mapList = (Map<String, List<ResultRealtimeCollector>>) map.get("list");
                }

                if (mapList != null) {
                    String key = params.get(Constants.field_name);
                    if (StringUtils.isEmpty(key)) {
                        continue;
                    }

                    int index = rowStart;

                    //遍历map中的值
                    for (List<ResultRealtimeCollector> list : mapList.values()) {
                        Map<String, Object> data = new LinkedHashMap<>();
                        data.put("row", index);
                        data.put("col", colIndex);
                        data.put("data", list);
                        data.put("key", key);
                        data.put("max-col", colEnd);
                        result.add(data);
                        index++;
                    }
                    if(maxRow < index){
                        maxRow = index;
                    }
                }
            }
        }
    }

    /**
     * 将数据写入文件
     *
     * @param path
     * @param sheetIndex
     * @param data
     * @return
     * @throws IOException
     */
    private boolean writeFile(String path, int sheetIndex, List<Map<String, Object>> data,int maxCol) throws
            IOException {
        FileOutputStream fileOutputStream = null;
        try {
            boolean version = false;
            HSSFWorkbook hssfWorkbook = null;
            HSSFSheet hssfSheet = null;
            XSSFWorkbook xssfWorkbook = null;
            XSSFSheet xssfSheet = null;
            // 获取模板对象,判断文件后缀名是xls，还是xlsx
            if (template.getSuffix().equals(".xls")) {
                // 获取HSSFWorkbook
                hssfWorkbook = getHSSFWorkbook2003(path);
                hssfSheet = hssfWorkbook.getSheetAt(sheetIndex);
                version = true;
            } else {
                xssfWorkbook = getHSSFWorkbook2007(path);
                xssfSheet = xssfWorkbook.getSheetAt(sheetIndex);
            }

            int index = 1;
            for (Map<String, Object> map : data) {
                int row = Utils.convertInt(String.valueOf(map.get("row")));
                int col = Utils.convertInt(String.valueOf(map.get("col")));
                String key = (String) map.get("key");
                List<ResultRealtimeCollector> item = (List<ResultRealtimeCollector>) map.get("data");

                String cellValue = getCellValue(item, key,index);
                Object cell = getCell(version, hssfSheet, xssfSheet, row, col);
                if (version) {
                    HSSFCell hssfCell = (HSSFCell) cell;
                    hssfCell.setCellValue(cellValue);
                } else {
                    XSSFCell xssfCell = (XSSFCell) cell;
                    xssfCell.setCellValue(cellValue);
                }
                index ++;
            }

            // 设置单元格格式
            for (int i = 0; i < maxRow; i++) {
                for (int j = 0; j < maxCol; j++) {
                    Object cell = getCell(version, hssfSheet, xssfSheet, i, j);
                    if (version) {
                        HSSFCell hssfCell = (HSSFCell) cell;
                        // 设置边框
                        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
                        cellStyle.setBorderBottom(BorderStyle.THIN);
                        cellStyle.setBorderLeft(BorderStyle.THIN);
                        cellStyle.setBorderTop(BorderStyle.THIN);
                        cellStyle.setBorderRight(BorderStyle.THIN);
                        // 设置边框
                        hssfCell.setCellStyle(cellStyle);
                    } else {
                        XSSFCell xssfCell = (XSSFCell) cell;
                        // 设置边框
                        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();
                        cellStyle.setBorderBottom(BorderStyle.THIN);
                        cellStyle.setBorderLeft(BorderStyle.THIN);
                        cellStyle.setBorderTop(BorderStyle.THIN);
                        cellStyle.setBorderRight(BorderStyle.THIN);
                        //cellStyle.setAlignment(HorizontalAlignment.CENTER);
                        // 设置边框
                        xssfCell.setCellStyle(cellStyle);
                    }
                }
            }

            fileOutputStream = new FileOutputStream(path);
            if (version) {
                hssfWorkbook.write(fileOutputStream);
            } else {
                xssfWorkbook.write(fileOutputStream);
            }
        } catch (Exception ex) {
            throw ex;
        } finally {
            if (fileOutputStream != null) {
                fileOutputStream.flush();
                fileOutputStream.close();
            }

        }

        return true;
    }

    private Object getCell(boolean version, HSSFSheet hssfSheet, XSSFSheet xssfSheet, int row, int col) {
        if (version) {
            // 2003 hssfWorkbook
            HSSFRow hssfRow = hssfSheet.getRow(row);
            if (hssfRow == null) {
                hssfRow = hssfSheet.createRow(row);
            }
            HSSFCell hssfCell = hssfRow.getCell(col);
            if (hssfCell == null) {
                hssfCell = hssfRow.createCell(col);
            }
            return hssfCell;
        } else {
            // 2007 xssfWorkbook
            XSSFRow xssfRow = xssfSheet.getRow(row);
            if (xssfRow == null) {
                xssfRow = xssfSheet.createRow(row);
            }
            XSSFCell xssfCell = xssfRow.getCell(col);
            if (xssfCell == null) {
                xssfCell = xssfRow.createCell(col);
            }
            return xssfCell;
        }
    }

    /**
     * 获取单元格数据
     *
     * @param data
     * @param key
     * @return
     */
    private String getCellValue(List<ResultRealtimeCollector> data, String key,int index) {
        for (ResultRealtimeCollector item : data) {
            String itemJson = JSONObject.toJSON(item).toString();
            Map<String, String> map = (Map<String, String>) JSONObject.parse(itemJson);
            map.put("index",String.valueOf(index));
            String val = "";
            if (key.startsWith("KPI")) {
                String kpiId = map.get("kpiId");
                if (kpiId.equals(key)) {
                    val = map.get("kpiValue");
                }
            } else {
                val = map.get(key);
            }

            if (StringUtils.isNotBlank(val)) {
                return val;
            }
        }
        return "";
    }

    /**
     * 复制文件
     *
     * @param template 模板信息
     * @return
     * @throws IOException
     */
    private String copyFile(Template template) throws IOException {
        String suffix = template.getSuffix();
        // 文件生成路径
        String reportPath = getReportPath(template.getReportPath(), template.getName(), suffix);
        // 获取模板信息
        File templateFile = new File(template.getPath());
        //  拷贝模板文件
        FileUtils.copyFile(templateFile, new File(reportPath));

        return reportPath;
    }

    /**
     * 创建HSSFWorkbook
     *
     * @param reportPath 生成文件路径
     * @return
     * @throws IOException
     */
    private HSSFWorkbook getHSSFWorkbook2003(String reportPath) throws IOException {

        // 获取文件
        FileInputStream stream = new FileInputStream(reportPath);
        POIFSFileSystem poifsFileSystem = new POIFSFileSystem(stream);

        // 获取HSSFWorkbook
        HSSFWorkbook sheets = new HSSFWorkbook(poifsFileSystem);

        poifsFileSystem.close();
        return sheets;

    }

    /**
     * 创建HSSFWorkbook
     *
     * @param reportPath 生成文件路径
     * @return
     * @throws IOException
     */
    private XSSFWorkbook getHSSFWorkbook2007(String reportPath) throws IOException {

        // 获取文件
        FileInputStream stream = new FileInputStream(reportPath);

        XSSFWorkbook sheets = new XSSFWorkbook(stream);
        stream.close();
        return sheets;

    }
}
